Cyber Security
Deploying C2 and EDR Analysis using Limacharlie and SysmonNetwork Traffic Analysis Tool using Python
Phishing Email Detection Tool using Python
Network Engineering
Secure AWS VPC Architecture and S3 IntegrationSecure Enterprise Network Implementation using Cisco Packet Tracer
Data Analyst
Full-Stack Data Warehouse with Medallion Architecture using SQLPower BI - IT Service Desk Dashboard
Power BI Dashboard Setup Guide
This guide will take you through setting up a Power BI dashboard from scratch, ensuring you understand the entire process, from importing data to creating meaningful visualizations.
1. Open Power BI
-
Launch Microsoft Power BI Desktop on your computer.
-
If you don’t have Power BI installed, download it from the Microsoft website.
2. Import Data into Power BI
-
Click on "Home" → "Get Data".
-
Select "Excel Workbook" if your data is in an Excel file.
(Other common sources: SQL Server, CSV, SharePoint, Power BI datasets, and APIs.) -
Navigate to your dataset (e.g., "ID Ticket Analysis.xlsx") and click "Open".
RAW DATASET TABLES :
- **IT_Agents:
** - **Tickets 1
**
3. Select Data Tables
-
A navigator window will open, displaying available tables in the Excel file.
-
Select the relevant tables:
-
IT_Agents Table
-
Tickets 1 Table
-
4. Transform Data in Power Query Editor
-
Click "Transform Data" before loading the tables.
-
Rename tables using the Properties column for clarity:
-
Rename "Table1" to "IT_Agents".
-
Rename "Table2" to "Tickets".
-
TRANSFORM Tickets Table :
-
Severity column has two values a numeric one and then a class is assigned to the column as well.We shall split this column into two separate column using the SPLIT COLUMN function.
We shall choose the Delimiter option for our project:
-
We shall action the same for the Priority table as well.
-
Rename columns (e.g., convert date fields to the "Date" format).
-
Resolution Time(Days) Standard SLA is 3.5 anything that is under this period that is within SLA otherwise it is outside SLA.
To implement this in the table we can add a custom column with a custom column formula.
change Data Type to text
TRANSFORM IT_Agents Table :
-
Full Name : There are columns that have aberrations for either the first name or the last name.
-
So , to make the process easier we shall remove the complete Full Name field and use the email field and Delimiter function to extract first name and last name columns.
-
In order to retrieve last name we shall select extract Text between delimiters :
-
Remove unnecessary spaces using Trim and Clean options Under Format tab.
-
We shall also capitalize each words so that the entered data is standardized.
-
Now we shall merge both the first name and last name columns.
-
We shall create a new column for the Date of birth as well since the year , month and date are three separate columns currently
Ensure to change the data type of the new Date of birth column to
-
We shall now create an Age column that showcases the age as of at 9/06/2025
Correcting the formatting for the age column
-
Click "Close & Apply" to save changes.
We can have a summarized view of each column by enabling the following options from the view tab
5. Create Relationships Between Tables
- Now for any data that is date based it is best practice to create a Calendar table which would enable us to draw relationships between all the other tables.
- Go to the Model View (third icon on the left panel).
- Create a new Calendar Table
- We shall now create a relation between the Date column in the Calendar table and the Date column in the Tickets table as shown below :
Now we need to create some standardization for our data. How can we make our current table easier to be related and understood.
One out of many ways could be instead of including both the priority key and priority value both in a single table, we can create a second table that has all the corresponding priority value that relate to each of the priority key values.
We can action the same for the Severity key and Severity Value as well.
As a result we shall only have the columns for Severity and Priority key in the Tickets table , we shall have two separate tables one of the Severity values for each key value and one of Priority values for each corresponding key values.
Lets get to it :
First we shall action it for the Severity Table
Now, we shall action the same for the Priority Table.
- Remove the Severity and Priority columns from the Tickets table as their corresponding values can be derived from the two new tables that we have created.
- Click Close and Apply.
6. Create Measures for Analysis
- Navigate to the Report View , Enter data and Name it Measures_Table
🎫🎟️TICKETS DASHBOARD
7. Customizing the Dashboard:
- **Canvas Size : 1080 px X 1920 px
** - **Customise the background and give it a Title:
** - **Slicer Visual:
**Change the format to drop down and add padding:
Post all formatting the Year Slicer should appear similar to:
- **Duplicate the Year Slicer to create Quater , Month and Day
**
8. Creating Measures
-
Total Tickets
Total Tickets = DISTINCTCOUNT(Tickets[ID Ticket])
Total Tickets = DISTINCTCOUNT(Tickets[ID Ticket]) -
Average Resolution Time ART
Average Resolution Time = AVERAGE(Tickets[Resolution Time (Days)])
Average Resolution Time = AVERAGE(Tickets[Resolution Time (Days)]) -
Average Satisfaction Rate ASR
Average Satisfaction Rate = AVERAGE(Tickets[Satisfaction Rate])
Average Satisfaction Rate = AVERAGE(Tickets[Satisfaction Rate]) -
Within SLA
Within SLA = CALCULATE([Total Tickets], Tickets[SLA Status] = "Within SLA")
Within SLA = CALCULATE([Total Tickets], Tickets[SLA Status] = "Within SLA") -
****SLA Breached
SLA Breached = CALCULATE([Total Tickets], Tickets[SLA Status]="SLA Breached")
SLA Breached = CALCULATE([Total Tickets], Tickets[SLA Status]="SLA Breached") -
SLA Compliant
SLA Compliant = DIVIDE([Within SLA], [Total Tickets])
SLA Compliant = DIVIDE([Within SLA], [Total Tickets]) -
SLA Non-compliant
SLA Non-Compliant = DIVIDE([SLA Breached], [Total Tickets])
SLA Non-Compliant = DIVIDE([SLA Breached], [Total Tickets])This has to be in percentage as well so action it similar to above.
9. Create a Dashboard (Report View)
-
Switch to Report View (first icon on the left).
-
Click "Insert" → "Card Visual": Add all the metrics from Measure_table
-
Formatted the Dashboard and added more effects to make it uniform:
-
Click "Insert" → "Bar Chart":
TOTAL TICKETS BY SEVERITY- Drag "Total Tickets" to the X-axis and "Severity" to the Y-axis.
- Make the necessary formatting changes to the chart.
TOTAL TICKETS BY PRIORITY - Copy the Bar chart that we created and change the Y-Axis to Priority.
- Also change the chart type to
.
- Once we change the chart type to Column instead of Bar, notice that X and Y axis are interchanged.
-
WATERFALL CHART based on the Ticket Category
TOTAL TICKETS by Year and Request Category- Copy the column chart so that we are not required to repeat the formatting for the chart.
Ascending chart will appear similar to !\[39ccee4d5c4537944df6af36a55b0a78.png\](:/e8ae3fe6cbb04aa886a7cda0d840d6c9)
-
TOTAL TICKETS by Quarter, Month and Request Category - AREA CHART
FINAL OVERVIEW OF THE TICKETS DASHBOARD :
IT AGENTS DASHBOARD
- Duplicate the Dash board above and Rename it to IT Agents Dashboard.
Creating Measures
-
Total IT Agents
Total IT Agents= DISTINCTCOUNT(Tickets[Agent ID])
Total IT Agents= DISTINCTCOUNT(Tickets[Agent ID]) -
Total Employees
Total Employees = DISTINCTCOUNT(Tickets[Employee ID]
Total Employees = DISTINCTCOUNT(Tickets[Employee ID] -
Average Age
Average Age = AVERAGE(IT_Agents[Age]
Average Age = AVERAGE(IT_Agents[Age] -
Agent Rating
Agent Rating = SWITCH( TRUE(), [Average Satisfaction Rate] < 4, REPT(UNICHAR(9733),3) & REPT(UNICHAR(9734),2), [Average Satisfaction Rate] < 5, REPT(UNICHAR(9733),4) & REPT(UNICHAR(9734),1) )
Agent Rating = SWITCH( TRUE(), [Average Satisfaction Rate] < 4, REPT(UNICHAR(9733),3) & REPT(UNICHAR(9734),2), [Average Satisfaction Rate] < 5, REPT(UNICHAR(9733),4) & REPT(UNICHAR(9734),1) )
VISUAL DASHBOARD - IT Agents
- Text Card(New) : For Average Age, Total Employees and Total IT Agents
- Table Visual :
SMART NARRATIVE
What is Smart Narrator (Smart Narrative)?
The Smart Narrative feature in Power BI helps create dynamic, AI-generated text summaries of your data visualizations. Instead of manually writing out insights, Power BI can automatically generate explanations, key trends, and data highlights from your visuals or entire report pages.
It essentially acts like a storyteller for your data.